<?php

/**
 * Project:     Framework G - G Light
 * File:        db.php
 * 
 * For questions, help, comments, discussion, etc., please join to the
 * website www.frameworkg.com
 * 
 * @link http://www.frameworkg.com/
 * @copyright 2013-02-07
 * @author Group Framework G  <info at frameworkg dot com>
 * @version 1.2
 */
class db {

    var $server = C_DB_SERVER; //DB server
    var $user = C_DB_USER; //DB user
    var $pass = C_DB_PASS; //DB password
    var $db = C_DB_DATABASE_NAME; //DB database name
    var $limit = C_DB_LIMIT; //DB limit of elements by page
    var $cn;
    var $numpages;
    static private $instance = null;

    private function __db() {
        
    }

    public static function getInstance() {
        if (self::$instance == null) {
            self::$instance = new db();
        }
        return self::$instance;
    }

    public function db() {
        
    }

    //connect to database
    public function connect() {
        $this->cn = mysql_connect($this->server, $this->user, $this->pass);
        if (!$this->cn) {
            die("Failed connection to the database: " . mysql_error());
        }
        if (!mysql_select_db($this->db)) {
            die("Unable to communicate with the database $db: " . mysql_error());
        }
        mysql_query("SET NAMES utf8");
    }

    //function for doing multiple queries
    public function do_operation($operation, $class = NULL) {
        $result = mysql_query($operation, $this->cn);
        if (!$result) {
            $this->throw_sql_exception($class);
        }
    }

    //function for obtain data from db in object form
    private function get_data($operation) {
        $data = array();
        $result = mysql_query($operation, $this->cn) or die(mysql_error($this->cn));
        while ($row = mysql_fetch_object($result)) {
            array_push($data, $row);
        }
        return $data;
    }

    //throw exception to web document
    private function throw_sql_exception($class) {
        $errno = mysql_errno();
        $error = mysql_error();
        $msg = $error . "<br /><br /><b>Error number:</b> " . $errno;
        throw new Exception($msg);
    }

    //for avoid sql injections, this functions cleans the variables
    private function escape_string(&$data) {
        if (is_object($data)) {
            foreach ($data->metadata() as $key => $attribute) {
                if (!is_empty($data->get($key))) {
                    $data->set($key, mysql_real_escape_string($data->get($key)));
                }
            }
        } else if (is_array($data)) {
            foreach ($data as $key => $value) {
                if (!is_array($value)) {
                    $data[$key] = mysql_real_escape_string($value);
                }
            }
        }
    }

    //function for add data to db
    public function insert($options, $object) {
        switch ($options['lvl1']) {
            case "user":
                switch ($options['lvl2']) {
                    case "normal":
                        //
                        break;
                }
                break;
            case "granjero":
                switch ($options['lvl2']) {
                    case "normal":
                        $id = mysql_real_escape_string($object->get('id'));
                        $name = mysql_real_escape_string($object->get('name'));
                        $sexo = mysql_real_escape_string($object->get('sexo'));
                        $edad = mysql_real_escape_string($object->get('edad'));
                        $this->do_operation("INSERT INTO granjero (id, name, edad, sexo) VALUES ('$id', '$name', '$edad', '$sexo');");
                        break;
                }
                break;
            case "finca":
                switch ($options['lvl2']) {
                    case "normal":
                        $id = mysql_real_escape_string($object->get('id'));
                        $name = mysql_real_escape_string($object->get('name'));
                        $valor_propiedad = mysql_real_escape_string($object->get('valor_propiedad'));
                        $cantidad_gallinas = mysql_real_escape_string($object->get('cantidad_gallinas'));
                        $cantidad_vacas = mysql_real_escape_string($object->get('cantidad_vacas'));
                        $granjero = mysql_real_escape_string($object->get('granjero'));
                        $this->do_operation("INSERT INTO finca (id, name, valor_propiedad, cantidad_gallinas, cantidad_vacas, granjero) VALUES ('$id', '$name', '$valor_propiedad', '$cantidad_gallinas', '$cantidad_vacas', '$granjero');");
                        break;
                }
                break;

            // Modulo
            case "modulo":
                switch ($options['lvl2']) {
                    case "normal":

                        $nombre = mysql_real_escape_string($object->get('nombre'));
                        $kernel = mysql_real_escape_string($object->get('kernel'));
                        $this->do_operation("INSERT INTO modulo (nombre, kernel) VALUES ('$nombre', '$kernel');");
                        break;
                }
                break;
            // Modulo
            case "peticion":
                switch ($options['lvl2']) {
                    case "normal":

                        $asunto = mysql_real_escape_string($object->get('asunto'));
                        $descripcion = mysql_real_escape_string($object->get('descripcion'));
                        $usuario = mysql_real_escape_string($object->get('usuario'));
                        $this->do_operation("INSERT INTO peticion (asunto, descripcion, estado_solucion, usuario, fecha_envio) VALUES ('$asunto','$descripcion', 'sin_solucionar', '$usuario', now());");
                        break;
                }
                break;
            case "respuesta":
                switch ($options['lvl2']) {
                    case "normal":

                        
                        $descripcion = mysql_real_escape_string($object->get('descripcion'));
                        $usuario = mysql_real_escape_string($object->get('usuario'));
                        $peticion = mysql_real_escape_string($object->get('peticion'));
                        $this->do_operation("INSERT INTO respuesta ( descripcion, peticion, usuario, fecha_envio) VALUES ('$descripcion', '$peticion', '$usuario', now());");
                        break;
                }
                break;
            case "calificacion":
                switch ($options['lvl2']) {
                    case "normal":

                        $usuariocalif = mysql_real_escape_string($object->get('usuariocalif'));
                        $usuariocoment = mysql_real_escape_string($object->get('usuariocoment'));
                        $respuesta = mysql_real_escape_string($object->get('respuesta'));

                        $this->do_operation("INSERT INTO calificacion (usuariocalif, usuariocoment, respuesta) VALUES ('$usuariocalif','$usuariocoment', '$respuesta');");
                        break;
                }
                break;
            // KERNEL 
            case "kernel":
                switch ($options['lvl2']) {
                    case "normal":
                        $version = mysql_real_escape_string($object->get('version'));
                        $descripcion = mysql_real_escape_string($object->get('descripcion'));

                        $this->do_operation("INSERT INTO kernel (version, descripcion, estado, fecha_inicio) VALUES ('$version','$descripcion', 'iniciado', now());");
                        break;
                }
                break;
            //  PROGRAMA
            case "programa":
                switch ($options['lvl2']) {
                    case "normal":
                        $nombre = mysql_real_escape_string($object->get('nombre'));
                        $funcionalidad = mysql_real_escape_string($object->get('funcionalidad'));
                        $codigo_fuente = mysql_real_escape_string($object->get('codigo_fuente'));

                        $programador = mysql_real_escape_string($object->get('programador'));

                        $this->do_operation("INSERT INTO programa (nombre, funcionalidad,codigo_fuente, programador, fecha_modificacion)
                                    VALUES ('$nombre','$funcionalidad','$codigo_fuente','$programador',now());");

                        break;
                    
                   case "normal2":
                        $nombre = mysql_real_escape_string($object->get('nombre'));
                        $funcionalidad = mysql_real_escape_string($object->get('funcionalidad'));
                        $codigo_fuente = mysql_real_escape_string($object->get('codigo_fuente'));

                        $programador = mysql_real_escape_string($object->get('programador'));
                        $modulo=  mysql_real_escape_string($object->get('modulo'));
                        $this->do_operation("INSERT INTO programa (nombre, funcionalidad,codigo_fuente, programador, fecha_modificacion, modulo)
                                    VALUES ('$nombre','$funcionalidad','$codigo_fuente','$programador',now(),'$modulo');");

                        break; 
                    
                }
                break;



            //  OBRA
            case "obra":
                switch ($options['lvl2']) {
                    case "normal":
                        $codigo = mysql_real_escape_string($object->get('codigo'));
                        $nombre = mysql_real_escape_string($object->get('nombre'));
                        $nombre_autor = mysql_real_escape_string($object->get('nombre_autor'));
                        $valor_estimado = mysql_real_escape_string($object->get('valor_estimado'));
                        $museo = mysql_real_escape_string($object->get('museo'));
                        $this->do_operation("INSERT INTO obra (codigo, nombre, nombre_autor, valor_estimado, museo) VALUES ('$codigo', '$nombre', '$nombre_autor', '$valor_estimado', '$museo');");
                        break;
                }
                break;
            default: break;
        }
    }

    //function for edit data from db
    public function update($options, $object) {
        switch ($options['lvl1']) {
            case "user":
                switch ($options['lvl2']) {
                    case "normal":
                        //
                        break;
                }
                break;
            case "kernel":
                switch ($options['lvl2']) {
                    case "by_id":
                        $id = mysql_real_escape_string($object->get('id'));
                        $this->do_operation("UPDATE kernel SET estado='finalizado', fecha_terminacion = now() WHERE id='$id';");
                        break;
                }
                break;

            case "peticion":
                switch ($options['lvl2']) {
                    case "by_id":
                        $id = mysql_real_escape_string($object->get('id'));
                        $this->do_operation("UPDATE peticion SET estado_solucion ='solucionado' WHERE id='$id' ;");
                        break;
                }
                break;

            case "programador":
                switch ($options['lvl2']) {
                    case "by_id_reputacion":
                        $cedula = mysql_real_escape_string($object->get('usuario'));
                        $reputacion = mysql_real_escape_string($object->get('reputacion'));
                        $this->do_operation("UPDATE programador SET reputacion = reputacion + '$reputacion' WHERE usuario='$cedula';");
                        break;
                }
                break;

            case "programa":
                switch ($options['lvl2']) {
                    case "by_id":
                        $id = mysql_real_escape_string($object->get('id'));
                        $modulo = mysql_real_escape_string($object->get('modulo'));
                        $this->do_operation("UPDATE programa SET modulo= '$modulo' WHERE id='$id';");
                        break;


                    case "by_editar":
                        $id = mysql_real_escape_string($object->get('id'));
                        $nombre = mysql_real_escape_string($object->get('nombre'));
                        $funcionalidad = mysql_real_escape_string($object->get('funcionalidad'));
                        $codigo_fuente = mysql_real_escape_string($object->get('codigo_fuente'));

                        $this->do_operation("UPDATE programa SET nombre= '$nombre',funcionalidad = '$funcionalidad ',codigo_fuente= '$codigo_fuente',fecha_modificacion=now() WHERE id='$id';");
                        break;

                    case "by_idprogram":
                        $id = mysql_real_escape_string($object->get('id'));
                        $this->do_operation("UPDATE programa SET fecha_envio= now() WHERE id='$id';");
                        break;
                    case "by_estado_requisito":
                        $id = mysql_real_escape_string($object->get('id'));
                        $estado_requisito = mysql_real_escape_string($object->get('estado_requisito'));
                        if ($estado_requisito == "cumplido") {
                            $this->do_operation("UPDATE programa SET estado_requisito = '$estado_requisito' WHERE id='$id';");
                        } else if ($estado_requisito == "no_cumplido") {
                            $this->do_operation("UPDATE programa SET estado_requisito = '$estado_requisito', fecha_envio = NULL WHERE id='$id';");
                        }

                        break;
                }
                break;

            default: break;
        }
    }

    //function for delete data from db
    public function delete($options, $object) {
        switch ($options['lvl1']) {
            case "user":
                switch ($options['lvl2']) {
                    case "normal":
                        //
                        break;
                }
                break;

            default: break;
        }
    }

    //function that returns an array with data from a operation
    public function select($option, $data) {
        $info = array();
        switch ($option['lvl1']) {
            case "programador":
                switch ($option['lvl2']) {
                    case "all":
                        $info = $this->get_data("SELECT * FROM programador;");
                        break;
                }
                break;
            case "usuario":
                switch ($option['lvl2']) {
                    case "all" : $info = $this->get_data("SELECT * FROM usuario;");
                        break;
                    case "by_peticion" : $info = $this->get_data("SELECT * FROM  usuario u where u.usuario IN (select usuario from peticion p where p.estado_solucion = 'sin_solucionar');");
                        break;
                    case "one_login":
                        $username = mysql_real_escape_string($data['username']);
                        $password = mysql_real_escape_string($data['password']);
                        $result = $this->get_data("SELECT password,cedula FROM usuario WHERE username = '$username';");
                        $cedula = $result[0]->cedula;
                        $hasher = new PasswordHash(8, FALSE);
                        if ($hasher->CheckPassword($password, $result[0]->password)) {
                            $info = $this->get_data("SELECT * FROM usuario WHERE cedula = '$cedula';");
                        }
                        unset($hasher);
                        break;
                    case "one_perfil" :
                        $username = mysql_real_escape_string($data['username']);
                        $info = $this->get_data("SELECT * FROM  usuario  where username='$username';");
                        break;
                }
                break;
            case "user":
                switch ($option['lvl2']) {
                    case "all" : $info = $this->get_data("SELECT * FROM user;");
                        break;
                    case "by_peticion" : $info = $this->get_data("SELECT * FROM  user u where u.user IN (select user from peticion p where p.estado_solucion = 'sin_solucionar');");
                        break;

                    case "one_login":
                        $user = $data['user'];
                        $password = $data['password'];
                        $result = $this->get_data("SELECT password, id FROM user WHERE user = '$user';");
                        $id = $result[0]->id;
                        $hasher = new PasswordHash(8, FALSE);
                        if ($hasher->CheckPassword($password, $result[0]->password)) {
                            $info = $this->get_data("SELECT name, user, type, id FROM user WHERE id = '$id';");
                        }
                        unset($hasher);
                        break;
                }
                break;

            case "boss":
                switch ($option['lvl2']) {
                    case "all":
                        $info = $this->get_data("SELECT * FROM boss;");
                        break;
                    case "one":
                        $id = mysql_real_escape_string($data['id']);
                        $info = $this->get_data("SELECT * FROM boss WHERE id='$id';");
                        break;
                }
                break;
            case "clerk":
                switch ($option['lvl2']) {
                    case "all":
                        $info = $this->get_data("SELECT * FROM clerk;");
                        break;
                    case "one":
                        $id = mysql_real_escape_string($data["id"]);
                        $info = $this->get_data("Select * from clerk where id= '$id';");
                        break;
                    case "by_boss":
                        $boss = mysql_real_escape_string($data['boss']);
                        $info = $this->get_data("SELECT * FROM clerk WHERE boss='$boss';");
                        break;
                }
                break;
            case "granjero":
                switch ($option['lvl2']) {
                    case "all":
                        $info = $this->get_data("SELECT * FROM granjero;");
                        break;
//                                case "one":					
//                                    $id=mysql_real_escape_string($data['id']);
//                                    $info=$this->get_data("SELECT * FROM boss WHERE id='$id';"); break;
                }
                break;

            case "finca":
                switch ($option['lvl2']) {
                    case "by_granjero":
                        $id = mysql_real_escape_string($data['id']);
                        $info = $this->get_data("SELECT * FROM finca where granjero = '$id';");
                        break;
//                                case "one":					
//                                    $id=mysql_real_escape_string($data['id']);
//                                    $info=$this->get_data("SELECT * FROM boss WHERE id='$id';"); break;
                }
                break;

            ////  MUSEO
            case "museo":
                switch ($option['lvl2']) {
                    case "all":
                        $info = $this->get_data("SELECT * FROM museo;");
                        break;
                    case "one":
                        $id = mysql_real_escape_string($data["codigo"]);
                        $info = $this->get_data("Select * from museo where codigo= '$id';");
                        break;
                }
                break;
            ////  Modulo
            case "modulo":
                switch ($option['lvl2']) {
                    case "all":
                        $info = $this->get_data("SELECT * FROM modulo;");
                        break;
                    case "by_kernel":
                        $kernel = mysql_real_escape_string($data['kernel']);
                        $info = $this->get_data("SELECT * FROM modulo WHERE kernel = '$kernel';");
                        break;
                    case "by_last":
                        $info = $this->get_data("SELECT MAX(id) As id FROM modulo;");
                        break;
                }
                break;

            //// PROGRAMA
            case "programa":
                switch ($option['lvl2']) {
                    case "all":
                        $info = $this->get_data("SELECT * FROM programa;");
                        break;
                    case "one":
                        $id = mysql_real_escape_string($data['id']);
                        $info = $this->get_data("SELECT * FROM programa where id = '$id';");
                        break;
                    case "by_modulo":
                        $modulo = mysql_real_escape_string($data['modulo']);
                        $info = $this->get_data("SELECT * FROM programa where modulo = '$modulo';");
                        break;

                    case "by_estado_null":
                        $modulo = mysql_real_escape_string($data['modulo']);
                        $info = $this->get_data("SELECT * FROM programa WHERE (estado_requisito IS NULL OR estado_requisito= 'no_cumplido') AND fecha_envio IS NOT NULL;");
                        break;
                    case "by_estado":
                        $info = $this->get_data("SELECT * FROM programa WHERE estado_requisito = 'cumplido' AND modulo is NULL;");
                        break;
                    case "by_programador":
                        $programadorId = mysql_real_escape_string($data['id_programador']);
                        $info = $this->get_data("SELECT * FROM programa WHERE  fecha_envio IS  NULL AND programador='$programadorId';");

                        break;

                    case "by_programador1":
                        $programadorId = mysql_real_escape_string($data['id_programador']);
                        $info = $this->get_data("SELECT * FROM programa WHERE programador='$programadorId';");

                        break;
                }

                break;


            ////  peticion
            case "peticion":
                switch ($option['lvl2']) {
                    case "all":
                        $info = $this->get_data("SELECT * FROM peticion order by fecha_envio;");
                        break;
                    case "by_state":
                        $info = $this->get_data("SELECT * FROM peticion WHERE estado_solucion='sin_solucionar';");
                        break;
                    case "by_user":

                        $info = $this->get_data("SELECT p.*, u.username As idUs FROM peticion p, usuario u where p.estado_solucion='sin_solucionar' and u.username = p.usuario order by p.fecha_envio;");
                        break;
                    case "by_oneuser":
                        $userUser = mysql_real_escape_string($data['usuario']);
                        $idPeticion = mysql_real_escape_string($data['id']);
                        $info = $this->get_data("SELECT * FROM peticion p where p.estado_solucion='sin_solucionar' and p.usuario = '$userUser' And p.id='$idPeticion';");
                        break;
                    case "count":
                        $info = $this->get_data("SELECT COUNT(*) AS COUNT FROM peticion;");
                        break;
                    case "by_paginacion":
                        $inicio = mysql_real_escape_string($data['inicio']);
                        $num_resultados = mysql_real_escape_string($data['num_resultados']);
                        $info = $this->get_data("SELECT * FROM peticion ORDER BY id DESC LIMIT $inicio,$num_resultados;");
                        break;
                }
                break;


            ////  peticion
            case "respuesta":
                switch ($option['lvl2']) {
                    case "all":
                        $info = $this->get_data("SELECT * FROM respuesta;");
                        break;
                    case "by_one":
                        $id = mysql_real_escape_string($data['id']);
                        $usuario = mysql_real_escape_string($data['usuario']);
                        $info = $this->get_data("SELECT * FROM respuesta where usuario='$usuario' AND id = '$id';");
                        break;
                }
                break;
            ////  K E R N E L
            case "kernel":
                switch ($option['lvl2']) {
                    case "all":
                        $info = $this->get_data("SELECT * FROM kernel;");
                        break;
                    case "by_state":
                        $info = $this->get_data("SELECT * FROM kernel WHERE estado='iniciado';");
                        break;
                    case "by_stateF":
                        $info = $this->get_data("SELECT * FROM kernel WHERE estado='finalizado';");
                        break;
                    case "by_one":
                        $id = mysql_real_escape_string($data['id']);
                        $info = $this->get_data("SELECT * FROM kernel WHERE id='$id';");
                        break;
                    case "by_last":
                        $info = $this->get_data("SELECT MAX(id) As id FROM kernel;");
                        break;
                }
                break;
            ////  OBRAS
            case "obra":
                switch ($option['lvl2']) {
                    case "all":
                        $info = $this->get_data("SELECT * FROM obra;");
                        break;
                    case "by_museo":
                        $id = mysql_real_escape_string($data['codigo']);
                        $info = $this->get_data("SELECT * FROM obra WHERE museo = '$id';");
                        break;
                }
                break;
            case "calificacion":
                switch ($option['lvl2']) {
                    case "all":
                        $info = $this->get_data("SELECT * FROM calificacion;");
                        break;
                    case "countPoint":
                        $username = mysql_real_escape_string($data['username']);
                        $info = $this->get_data("select  * from calificacion where usuariocoment ='$username';");
                        break;
                }

                break;

            default: break;
        }
        return $info;
    }

    //close the db connection
    public function close() {
        if ($this->cn) {
            mysql_close($this->cn);
        }
    }

}

?>